package com.jianglin.mapper;

import com.jianglin.model.Owner;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;

@Mapper
@Component("ownerDao")
public interface OwnerMapper  {
    // 使用PageHelper分页查询
    @Select("<script>" +
            "SELECT own.*, hou.numbers as house_numbers " +
            "FROM owner own " +
            "LEFT JOIN house hou ON hou.id = own.house_id " +
            "<where>" +
            "   <if test='tel != null'> and own.tel like CONCAT('%', #{tel}, '%') </if>" +
            "   <if test='identity != null'> and own.identity like CONCAT('%', #{identity}, '%') </if>" +
            "</where>" +
            "order by own.id desc" +
            "</script>")
    @Results(id = "ownerMap", value = {
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "tel", column = "tel"),
            @Result(property = "sex", column = "sex"),
            @Result(property = "identity", column = "identity"),
            @Result(property = "houseId", column = "house_id"),
            @Result(property = "remarks", column = "remarks"),
            @Result(property = "password", column = "password"),
            @Result(property = "house.numbers", column = "house_numbers")
    })
    List<Owner> queryOwnerAll(Owner owner);

    @Delete("DELETE FROM owner WHERE username = #{username2}")
    void deleteOwnerUserByUserName(@Param("username2") String username);

    @Select("SELECT * FROM owner WHERE id = #{id}")
    Owner queryOwnerById(@Param("id") long id);

    @Select("SELECT * FROM owner WHERE username = #{username}")
    Owner queryOwnerByName(String username);

    // 基础CRUD操作
    @Insert("INSERT INTO owner (username, tel, sex, identity, house_id, remarks, password) " +
            "VALUES (#{username}, #{tel}, #{sex}, #{identity}, #{houseId}, #{remarks}, #{password})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertOwner(Owner owner);

    @Update("<script>" +
            "UPDATE owner " +
            "<set>" +
            "   <if test='username != null'>username=#{username},</if>" +
            "   <if test='tel != null'>tel=#{tel},</if>" +
            "   <if test='sex != null'>sex=#{sex},</if>" +
            "   <if test='identity != null'>identity=#{identity},</if>" +
            "   <if test='houseId != null'>house_id=#{houseId},</if>" +
            "   <if test='remarks != null'>remarks=#{remarks},</if>" +
            "   <if test='password != null'>password=#{password}</if>" +
            "</set>" +
            "WHERE id=#{id}" +
            "</script>")
    int updateOwner(Owner owner);

    @Delete("DELETE FROM owner WHERE id = #{id}")
    int deleteOwnerById(Long id);

    @Select("SELECT * FROM owner")
    List<Owner> findAll();

    @Select("SELECT * FROM owner WHERE id = #{ownerId}")
    Owner findOwnerById(Long ownerId);

    @Select("SELECT * FROM owner WHERE id = #{id}")
    Owner findById(Integer id);
}
